using System;
using System.Text;
using System.Reflection;
using System.Collections.Generic;
using System.Security.Cryptography;

using Crainiate.Data.Persistence.Cache;
using Crainiate.Data.Persistence.Providers;
using Crainiate.Data.Persistence.Providers.Labelling;

namespace Crainiate.Data.Persistence.Providers.SqlServer
{
	public sealed class SqlContext: Context
	{
        //Property variables
        private string _server;
        private string _database;
        private bool _integrated;
        private bool _pooling;
        private string _userId;
        private string _password;
        private string _custom;

        //Working variables
        private string _connectionString;
        private bool _databaseValid;
        private Dictionary<string, bool> _procedures;

        private bool _flag;

        #region Interface

        //Constructor
        public SqlContext(): base()
        {
            //Working variables
            _connectionString = null;
            _databaseValid = false;
            _procedures= new Dictionary<string, bool>();

            //Properties
            Server = "(local)";
            Database = "Objecto";
            Integrated = true;
            Pooling = true;
        }

        //Properties
        public string Server
        {
            get 
            {
                return _server; 
            }
            set 
            {
                if (_server != value)
                {
                    _server = value;
                    _connectionString = null;
                    _databaseValid = false;
                }
            }
        }

        public string Database
        {
            get 
            { 
                return _database; 
            }
            set 
            {
                if (_database != value)
                {
                    _database = value;
                    _connectionString = null;
                    _databaseValid = false;
                }
            }
        }

        public string UserId
        {
            get 
            {
                return _userId; 
            }
            set
            {
                if (_userId != value)
                {
                    _userId = value;
                    _connectionString = null;
                    _databaseValid = false;
                }
            }
        }

        public string Password
        {
            get 
            { 
                return _password; 
            }
            set
            {
                if (_password != value)
                {
                    _password = value;
                    _connectionString = null;
                    _databaseValid = false;
                }
            }
        }

        public bool Integrated
        {
            get 
            { 
                return _integrated; 
            }
            set 
            {
                if (_integrated != value)
                {
                    _integrated = value;
                    _connectionString = null;
                    _databaseValid = false;
                }
            }
        }

        public bool Pooling
        {
            get 
            { 
                return _pooling; 
            }
            set 
            { 
                _pooling = value; 
            }
        }

        public bool IsValid
        {
            get
            {
                return _databaseValid;
            }
        }

        public string Custom
        {
            get
            {
                return _custom;
            }
            set
            {
                if (_custom != value)
                {
                    _connectionString = null;
                    _databaseValid = false;
                    _custom = value;
                }
            }
        }

        //Methods
        public override Command CreateCommand()
        {
            return new SqlCommand(this);
        }

        public override Concurrency CreateConcurrency()
        {
            return new Concurrency();
        }

        public override Formatter CreateFormatter()
        {
            return new SqlFormatter();
        }

        public string GetConnectionString()
        {
            if (_custom != null && _custom != string.Empty) return _custom;
            if (_connectionString == null) _connectionString = CreateConnectionString();
            return _connectionString;
        }

        //Returns an open connection
        public System.Data.SqlClient.SqlConnection GetConnection()
        {
            //Validate and create database if necessary
            if (!IsValid)
            {
                if (!CheckDatabase()) throw new ContextException("The requested database was not found. Use the SqlDeployment class to create a database if required.");
                SetValid(true); // must be set after check and create as they use the connection string
            }
            return GetConnectionInternal();
        }

        public string GetProcedure(Type type, ProcedureType procedureType)
        {
            if (procedureType == ProcedureType.Select)
            {
                return GetSelectProcedure(type);
            }
            else if (procedureType == ProcedureType.Insert)
            {
                return GetCreateProcedure(type);
            }
            else if (procedureType == ProcedureType.Update)
            {
                return GetUpdateProcedure(type);
            }
            else if (procedureType == ProcedureType.Delete)
            {
                return GetDeleteProcedure(type);
            }
            else if (procedureType == ProcedureType.Exists)
            {
                return GetExistsProcedure(type);
            }

            return null;
        }

        public string GetProcedure(IQuery query)
        {
            return GetQueryProcedure(query);
        }

        //Set the database created flag
        public void SetValid(bool created)
        {
            _databaseValid = created;
        }

		#endregion

        #region Overrides

        public override int GetHashCode()
        {
            StringBuilder builder = new StringBuilder();
            builder.Append(this.GetType().ToString());
            builder.Append(Server);
            builder.Append(Database);
            builder.Append(UserId);
            builder.Append(Prefix);

            return builder.ToString().GetHashCode();
        }

        #endregion

        #region Implementation

        //Create a database connection string
        internal string CreateConnectionString()
        {
            StringBuilder builder = new StringBuilder();
            
            builder.Append("Server=");
            builder.Append(Server);
            builder.Append(";Database=");
            builder.Append(Database);

            //Turn off pooling
            if (!Pooling) builder.Append(";Pooling=False");

            if (Integrated)
            {
                builder.Append(";Integrated Security=SSPI");
            }
            else
            {
                builder.Append(";User ID=");
                builder.Append(UserId);
                builder.Append(";Password=");
                builder.Append(Password);
            }
            return builder.ToString();
        }

        //Determines if the database specified by the Database property exists
        private bool CheckDatabase()
        {
            //Save current database name
            string current = Database;

            try
            {
                StringBuilder builder = new StringBuilder();
                builder.Append("SELECT 1 FROM sys.master_files m ");
                builder.Append("WHERE lower(db_name(m.database_id)) = '");
                builder.Append(current.ToLower());
                builder.Append("' GROUP by m.database_id");

                //Get a connection to master
                Database = "master";
                System.Data.SqlClient.SqlConnection conn = GetConnectionInternal(); //Use internal conenction method so that not revalidated
                System.Data.SqlClient.SqlCommand command = conn.CreateCommand();
                command.CommandText = builder.ToString();

                try
                {
                    if (command.ExecuteScalar() != null)
                    {
                        Database = current;
                        return true;
                    }
                }
                finally
                {
                    command.Dispose();
                    conn.Dispose();
                }
            }
            catch 
            {
                //Restore current database name
                Database = current;

                //If there was an exception then there may have been a permission issue accessing Master
                //return true so that processing can continue
                return true;
            }

            //Restore current database name
            Database = current;
            return false;
        }

        //Returns an open sql connection without checking for a valid database
        //Do not open the connection
        internal System.Data.SqlClient.SqlConnection GetConnectionInternal()
        {
            //Build up the connection string
            System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();

            conn.ConnectionString = GetConnectionString();
            conn.Open();

            string[] version = conn.ServerVersion.Split(".".ToCharArray());
            int major = 0;
            if (!int.TryParse(version[0], out major) || major < 9) throw new ContextException("Sql Server must be version 2005 or later.");

            return conn;
        }

        private string GetSelectProcedure(Type type)
        {
            StringBuilder builder = new StringBuilder();

            //Get the name for the type
            builder.Append(Common.GetTypeName(type, Prefix));
            builder.Append(".Select");

            string procedure = builder.ToString();

            //Check to see if the procedure has been deployed
            if (_procedures.ContainsKey(procedure)) return procedure;

            System.Data.SqlClient.SqlConnection conn = GetConnectionInternal();
            try
            {
                if (!ProcedureExists(procedure, conn)) CreateSelectProcedure(conn, procedure, type);
            }
            finally
            {
                conn.Dispose();
            }

            _procedures.Add(procedure, true);

            return procedure;
        }

        private void CreateSelectProcedure(System.Data.SqlClient.SqlConnection connection, string name, Type type)
        {
            ReflectionCacheItem cache = Component.Instance.ReflectionCache.Lookup(type);
            PropertyInfo[] filter = null;
           
            //Switch to persisted type, keep properties in filter reference
            if (cache.IsView)
            {
                filter = cache.Properties;
                
                type = cache.PersistType;
                cache = Component.Instance.ReflectionCache.Lookup(type);
            }

            StringBuilder builder = new StringBuilder();

            builder.Append("CREATE PROCEDURE [");
            builder.Append(name);
            builder.Append("] ");
            builder.Append("\r\n");

            //Get the key parameters
            PropertyInfo[] keys = cache.GetKeyProperties();

            bool flag = false;
            foreach (PropertyInfo prop in keys)
            {
                if (flag) builder.Append(",");

                builder.Append("@");
                builder.Append(SqlFormatter.GetPropertyName(prop, ""));
                builder.Append(" ");
                builder.Append(SqlCommon.SqlFromProperty(prop));

                flag = true;
            }

            //Get the abbreviations for the procedure
            Labels labels = new Labels(type);
            TypeLabelHash label = new TypeLabelHash(type);
            TypeLabelHash underlyingLabel = TypeLabelHash.GetBase(label);

            builder.Append(" AS \r\nSELECT ");
            SelectPropertyFields(builder, type, cache, labels, label, filter, null);

            builder.Append(" \r\nFROM [");
            builder.Append(Common.GetTypeName(cache.UnderlyingType, Prefix));
            builder.Append("] ");
            builder.Append(labels[underlyingLabel]);

            //Join to any inherited tables
            if (!cache.IsUnderlying) SelectBaseJoin(builder, type, cache, labels, label, JoinType.Inner);

            //Join any object properties
            if (cache.HasObjects || !cache.IsUnderlying) SelectObjectPropertyJoins(builder, type, cache, labels, label, filter);

            //Append the where by key
            flag = false;

            builder.Append(" \r\nWHERE ");
            foreach (PropertyInfo prop in keys)
            {
                if (flag) builder.Append(" AND ");

                builder.Append(labels[underlyingLabel]);
                builder.Append(".[");
                builder.Append(SqlFormatter.GetPropertyName(prop, "."));
                builder.Append("]");
                builder.Append("=@");
                builder.Append(SqlFormatter.GetPropertyName(prop, ""));

                flag = true;
           }

           System.Data.SqlClient.SqlCommand command = connection.CreateCommand();
           command.CommandText = builder.ToString();
           command.ExecuteNonQuery();
           command.Dispose();
        }

        //Joins fields and base fields if required
        private void SelectPropertyFields(StringBuilder builder, Type type, ReflectionCacheItem cache, Labels labels, LabelHash label, PropertyInfo[] filter, IColumns aggregates)
        {   
            bool flag = false;

            if (!cache.IsUnderlying)
            {
                ReflectionCacheItem baseCache = Component.Instance.ReflectionCache.Lookup(cache.BaseType);
                TypeLabelHash baseLabel = new TypeLabelHash(cache.BaseType, label);
                SelectPropertyFields(builder, cache.BaseType, baseCache, labels, baseLabel, filter, aggregates);

                builder.Append(",\r\n");
            }

            foreach (PropertyInfo prop in cache.GetKeyProperties())
            {
                if (flag) builder.Append(",");

                //Append any aggregates
                string aggregate = GetAggregate(prop, aggregates);
                if (aggregate != null)
                {
                    builder.Append(aggregate);
                    builder.Append("(");
                }

                //Add the key name
                builder.Append(labels[label]);
                builder.Append(".[");
                builder.Append(SqlFormatter.GetPropertyName(prop, "."));
                builder.Append("]");

                if (aggregate != null) builder.Append(")");
                
                flag = true;
            }

            PropertyInfo[] properties = cache.Properties;
            if (filter != null) properties = Crainiate.Data.Persistence.Providers.Common.GetCommonProperties(properties, filter);
            
            foreach (PropertyInfo prop in properties)
            {
                if (flag) builder.Append(",");

                //Append any aggregates
                string aggregate = GetAggregate(prop, aggregates);
                if (aggregate != null)
                {
                    builder.Append(aggregate);
                    builder.Append("(");
                }

                builder.Append(labels[label]);
                builder.Append(".");
                builder.Append("[");
                builder.Append(SqlFormatter.GetPropertyName(prop, "."));
                builder.Append("]");

                if (aggregate != null) builder.Append(")");

                flag = true;
            }

            //Select fields for joined object property
            if (cache.HasObjects)
            {
                properties = cache.ReferenceProperties;
                if (filter != null) properties = Crainiate.Data.Persistence.Providers.Common.GetCommonProperties(properties, filter);

                foreach (PropertyInfo prop in properties)
                {
                    Type propType = prop.PropertyType;
                    ReflectionCacheItem propCache = Component.Instance.ReflectionCache.Lookup(propType);
                    PropertyInfo[] propfilter = null;
                    PropertyLabelHash propertyLabel = new PropertyLabelHash(prop, label);

                    //Switch to persisted type, keep properties in filter reference
                    if (propCache.IsView)
                    {
                        propfilter = propCache.Properties;

                        propType = propCache.PersistType;
                        propCache = Component.Instance.ReflectionCache.Lookup(propType);
                    }

                    builder.Append(",\r\n");
                    SelectPropertyFields(builder, propType, propCache, labels, propertyLabel, null, null);
                }
            }
        }

        //Joins a base type to a type
        private void SelectBaseJoin(StringBuilder builder, Type type, ReflectionCacheItem cache, Labels labels, LabelHash label, JoinType joinType)
        {
            if (cache.IsUnderlying) return;
            
            //Recurse for further inheritance joins
            ReflectionCacheItem baseCache = Component.Instance.ReflectionCache.Lookup(cache.BaseType);
            TypeLabelHash baseLabel = new TypeLabelHash(cache.BaseType, label);
            SelectBaseJoin(builder, cache.BaseType, baseCache, labels, baseLabel, joinType);

            builder.Append(" \r\n");
            builder.Append(SqlCommon.SqlFromJoin(joinType));
            builder.Append("JOIN [");
            builder.Append(Common.GetTypeName(type, Prefix));
            builder.Append("] ");
            builder.Append(labels[label]);
            builder.Append(" ON ");

            //Join field names are always the same because of inheritance
            if (cache.HasPrimaryKey)
            {
                string name = SqlFormatter.GetPropertyName(cache.PrimaryKey, ".");

                builder.Append(labels[baseLabel]);
                builder.Append(".[");
                builder.Append(name);
                builder.Append("] = ");
                builder.Append(labels[label]);
                builder.Append(".[");
                builder.Append(name);
                builder.Append("]");
            }
            else if (cache.HasCompoundKeys)
            {
                bool flag = false;

                foreach (PropertyInfo prop in cache.CompoundKeys)
                {
                    string name = SqlFormatter.GetPropertyName(prop, ".");

                    if (flag) builder.Append(" AND ");

                    builder.Append(labels[baseLabel]);
                    builder.Append(".[");
                    builder.Append(name);
                    builder.Append("] = ");
                    builder.Append(labels[label]);
                    builder.Append(".[");
                    builder.Append(name);
                    builder.Append("]");

                    flag = true;
                }
            }
        }

        private void SelectObjectPropertyJoins(StringBuilder builder, Type type, ReflectionCacheItem cache, Labels labels, LabelHash label, PropertyInfo[] filter)
        {   
            //Perform any object property joins for underlying classes first
            if (!cache.IsUnderlying)
            {
                ReflectionCacheItem baseCache = Component.Instance.ReflectionCache.Lookup(cache.BaseType);
                TypeLabelHash baseLabel = new TypeLabelHash(cache.BaseType, label);
                SelectObjectPropertyJoins(builder, cache.BaseType, baseCache, labels, baseLabel, null);
            }

            //Process any joins for object properties
            if (cache.HasObjects)
            {
                PropertyInfo[] properties = cache.ReferenceProperties;
                if (filter != null) properties = Crainiate.Data.Persistence.Providers.Common.GetCommonProperties(properties, filter);

                foreach (PropertyInfo prop in properties)
                {
                    //Join to the underlying type using a left join
                    ReflectionCacheItem propCache = Component.Instance.ReflectionCache.Lookup(prop.PropertyType);
                    PropertyLabelHash propLabel = new PropertyLabelHash(prop, label);
                    LabelHash underlyingLabel = PropertyLabelHash.GetBase(propLabel); //Can return a prop or type label

                    string hash1 = propLabel.GetHashCode().ToString();
                    string hash2 = underlyingLabel.GetHashCode().ToString();

                    builder.Append(" \r\nLEFT JOIN [");
                    builder.Append(Common.GetTypeName(propCache.UnderlyingType, Prefix));
                    builder.Append("] ");
                    builder.Append(labels[underlyingLabel]);
                    builder.Append(" ON ");

                    ReflectionCacheItem underlyingCache = Component.Instance.ReflectionCache.Lookup(propCache.UnderlyingType);

                    builder.Append(labels[label]);
                    builder.Append(".[");
                    builder.Append(SqlFormatter.GetPropertyName(prop, "."));
                    builder.Append("] = ");
                    builder.Append(labels[underlyingLabel]);
                    builder.Append(".[");
                    builder.Append(SqlFormatter.GetPropertyName(underlyingCache.PrimaryKey, "."));
                    builder.Append("]");

                    //Recurse incase any of the properties of the joined reference have their own reference properties
                    SelectObjectPropertyJoins(builder, propCache.PersistType, propCache, labels, propLabel, null);

                    //Recurse from the bottom of the inheritance chain for this property
                    if (!propCache.IsUnderlying)
                    {
                        SelectBaseJoin(builder, prop.PropertyType, propCache, labels, propLabel, JoinType.Left);
                    }
                }
            }
        }

        private bool ProcedureExists(string procedure, System.Data.SqlClient.SqlConnection conn)
        {
            //Check if there is a procedure
            StringBuilder builder = new StringBuilder();
            builder.Append("SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE SPECIFIC_NAME = '");
            builder.Append(procedure);
            builder.Append("'");

            System.Data.SqlClient.SqlCommand command = conn.CreateCommand();
            command.CommandText = builder.ToString();

            return (command.ExecuteScalar() != null);
        }

        private string GetCreateProcedure(Type type)
        {
            StringBuilder builder = new StringBuilder();

            //Get the name for the type
            builder.Append(Common.GetTypeName(type, Prefix));
            builder.Append(".Insert");

            string procedure = builder.ToString();

            //Check to see if the procedure has been deployed
            if (_procedures.ContainsKey(procedure)) return procedure;

            System.Data.SqlClient.SqlConnection conn = GetConnectionInternal();
            try
            {
                if (!ProcedureExists(procedure, conn)) CreateInsertProcedure(conn, procedure, type);
            }
            finally
            {
                conn.Dispose();
            }

            _procedures.Add(procedure, true);

            return procedure;
        }

        private void CreateInsertProcedure(System.Data.SqlClient.SqlConnection connection, string name, Type type)
        {
            StringBuilder builder = new StringBuilder();

            builder.Append("CREATE PROCEDURE [");
            builder.Append(name);
            builder.Append("] ");
            builder.Append("\r\n");

            ReflectionCacheItem cache = Component.Instance.ReflectionCache.Lookup(type);

            bool flag = false;
            GetInsertProcedureParameters(builder, type, cache, ref flag);

            //Add an output parameter if an identity
            if (cache.IsUnderlying && cache.IsIdentity)
            {
                if (flag) builder.Append(",");
                builder.Append("@_identity ");
                builder.Append(SqlCommon.SqlFromProperty(cache.PrimaryKey));
                builder.Append(" OUTPUT");
            }

            builder.Append(" AS");

            CreateInsertSelection(builder, type, cache);

            System.Data.SqlClient.SqlCommand command = connection.CreateCommand();
            command.CommandText = builder.ToString();
            command.ExecuteNonQuery();
            command.Dispose();
        }

               //Writes out parameters from base class back up inheritance chain
        private void GetInsertProcedureParameters(StringBuilder builder, Type type, ReflectionCacheItem cache, ref bool flag)
        {
            //Add keys that are not identities
            foreach (PropertyInfo prop in cache.GetKeyProperties())
            {
                if (prop != cache.PrimaryKey || !cache.IsIdentity || (cache.IsIdentity && !cache.IsUnderlying))
                {
                    if (flag) builder.Append(",");

                    //Get the type of the key
                    builder.Append("@");
                    builder.Append(SqlFormatter.GetPropertyName(prop, ""));
                    builder.Append(" ");
                    builder.Append(SqlCommon.SqlFromProperty(prop));

                    flag = true;
                }
            }

            //Add the other values
            foreach (PropertyInfo prop in cache.Properties)
            {
                if (flag) builder.Append(",");

                //Get the type of the key
                builder.Append("@");
                builder.Append(SqlFormatter.GetPropertyName(prop, ""));
                builder.Append(" ");
                builder.Append(SqlCommon.SqlFromProperty(prop));

                flag = true;
            }
        }

        private void CreateInsertSelection(StringBuilder builder, Type type, ReflectionCacheItem cache)
        {
            builder.Append(" \r\nINSERT INTO [");
            builder.Append(Common.GetTypeName(type, Prefix));
            builder.Append("] \r\n(");

            bool flag = false;

            //Add the key fields checking for the identity
            foreach (PropertyInfo prop in cache.GetKeyProperties())
            {
                if (prop != cache.PrimaryKey || !cache.IsIdentity || (cache.IsIdentity && !cache.IsUnderlying))
                {
                    if (flag) builder.Append(",");
                    builder.Append("[");
                    builder.Append(SqlFormatter.GetPropertyName(prop, "."));
                    builder.Append("]");

                    flag = true;
                }
            }

            //Add normal properties
            foreach (PropertyInfo prop in cache.Properties)
            {
                if (flag) builder.Append(",");

                //Get the type of the key
                builder.Append("[");
                builder.Append(SqlFormatter.GetPropertyName(prop, "."));
                builder.Append("]");

                flag = true;
            }

            builder.Append(") \r\nSELECT ");
            flag = false;

            //Add keys that are not identities
            foreach (PropertyInfo prop in cache.GetKeyProperties())
            {
                if (prop != cache.PrimaryKey || !cache.IsIdentity || (cache.IsIdentity && !cache.IsUnderlying))
                {
                    if (flag) builder.Append(",");
                    builder.Append("@");
                    builder.Append(SqlFormatter.GetPropertyName(prop, ""));

                    flag = true;
                }
            }

            //Add values
            foreach (PropertyInfo prop in cache.Properties)
            {
                if (flag) builder.Append(",");
                
                builder.Append("@");
                builder.Append(SqlFormatter.GetPropertyName(prop, ""));
                
                flag = true;
            }

            //Return output parameter if Identity, so that objects higher up the chain can use the @_identity
            if (cache.IsUnderlying && cache.IsIdentity) builder.Append(" \r\nSELECT @_identity = SCOPE_IDENTITY()");
        }

        private string GetUpdateProcedure(Type type)
        {
            StringBuilder builder = new StringBuilder();

            //Get the name for the type
            builder.Append(Common.GetTypeName(type, Prefix));
            builder.Append(".Update");

            string procedure = builder.ToString();

            //Check to see if the procedure has been deployed
            if (_procedures.ContainsKey(procedure)) return procedure;

            System.Data.SqlClient.SqlConnection conn = GetConnectionInternal();

            try
            {
                if (!ProcedureExists(procedure, conn)) CreateUpdateProcedure(conn, procedure, type);
            }
            finally
            {
                conn.Dispose();
            }

            _procedures.Add(procedure, true);

            return procedure;
        }

        private void CreateUpdateProcedure(System.Data.SqlClient.SqlConnection connection, string name, Type type)
        {
            //Create two versions, one with concurrency and one without
            CreateUpdateProcedureDetail(connection, name, type, true);
            CreateUpdateProcedureDetail(connection, name, type, false);
        }

        private void CreateUpdateProcedureDetail(System.Data.SqlClient.SqlConnection connection, string name, Type type, bool concurrency)
        {
            StringBuilder builder = new StringBuilder();

            builder.Append("CREATE PROCEDURE [");
            builder.Append(name);
            builder.Append("]");

            //Add the number depending on the concurrency
            if (!concurrency) builder.Append(";2");
            builder.Append("\r\n");

            ReflectionCacheItem cache = Component.Instance.ReflectionCache.Lookup(type);

            GetUpdateProcedureParameters(builder, type, cache, concurrency);

            builder.Append(" AS ");

            //Add the conditional if statements to populate the unchanged parameters
            if (concurrency)
            {
                foreach (PropertyInfo prop in cache.Properties)
                {
                    if (Deployment.GetTypeConcurrency(prop.PropertyType))
                    {
                        string fieldname = SqlFormatter.GetPropertyName(prop, "");

                        builder.Append("\r\nIF ");

                        //Get the type of the key
                        builder.Append("@");
                        builder.Append(fieldname);
                        builder.Append("Old IS NULL SELECT @");
                        builder.Append(fieldname);
                        builder.Append("Old = @");
                        builder.Append(fieldname);
                        builder.Append(" ");
                    }
                }
            }

            builder.Append("\r\nUPDATE [");
            builder.Append(Common.GetTypeName(type, Prefix));
            builder.Append("] \r\nSET ");

            bool flag = false;

            //Add the fields to be updated
            foreach (PropertyInfo prop in cache.Properties)
            {
                if (flag) builder.Append(",");

                builder.Append("[");
                builder.Append(SqlFormatter.GetPropertyName(prop, "."));
                builder.Append("] = @");
                builder.Append(SqlFormatter.GetPropertyName(prop, ""));

                flag = true;
            }

            //Add the key where clause
            builder.Append(" \r\nWHERE ");

            flag = false;
            foreach (PropertyInfo prop in cache.GetKeyProperties())
            {
                if (flag) builder.Append(" AND ");

                builder.Append("[");
                builder.Append(SqlFormatter.GetPropertyName(prop, "."));
                builder.Append("] = @");
                builder.Append(SqlFormatter.GetPropertyName(prop, ""));

                flag = true;
            }

            //Add the concurrency where clause
            if (concurrency)
            {
                foreach (PropertyInfo prop in cache.Properties)
                {
                    if (Deployment.GetTypeConcurrency(prop.PropertyType))
                    {
                        string col = SqlFormatter.GetPropertyName(prop, ".");

                        if (flag) builder.Append(" AND ");
                        builder.Append("([");
                        builder.Append(col);
                        builder.Append("] = @");
                        builder.Append(SqlFormatter.GetPropertyName(prop, ""));
                        builder.Append("Old OR [");
                        builder.Append(col);
                        builder.Append("] IS NULL) ");
                    }
                }
            }

            System.Data.SqlClient.SqlCommand command = connection.CreateCommand();
            command.CommandText = builder.ToString();
            command.ExecuteNonQuery();
            command.Dispose();
        }

        private string GetDeleteProcedure(Type type)
        {
            StringBuilder builder = new StringBuilder();

            //Get the name for the type
            builder.Append(Common.GetTypeName(type, Prefix));
            builder.Append(".Delete");

            string procedure = builder.ToString();

            //Check to see if the procedure has been deployed
            if (_procedures.ContainsKey(procedure)) return procedure;

            System.Data.SqlClient.SqlConnection conn = GetConnectionInternal();

            try
            {
                if (!ProcedureExists(procedure, conn)) CreateDeleteProcedure(conn, procedure, type);
            }
            finally
            {
                conn.Dispose();
            }

            _procedures.Add(procedure, true);

            return procedure;
        }

        private void CreateDeleteProcedure(System.Data.SqlClient.SqlConnection connection, string name, Type type)
        {
            StringBuilder builder = new StringBuilder();

            builder.Append("CREATE PROCEDURE [");
            builder.Append(name);
            builder.Append("] ");
            builder.Append("\r\n");

            //Get the key cache
            ReflectionCacheItem cache = Component.Instance.ReflectionCache.Lookup(type);
            bool flag = false;

            foreach (PropertyInfo prop in cache.GetKeyProperties())
            {
                if (flag) builder.Append(",");

                builder.Append("@");
                builder.Append(SqlFormatter.GetPropertyName(prop, ""));
                builder.Append(" ");
                builder.Append(SqlCommon.SqlFromProperty(prop));

                flag = true;
            }

            builder.Append(" AS \r\nDELETE FROM [");
            builder.Append(Common.GetTypeName(type, Prefix));
            builder.Append("] \r\nWHERE ");

            flag = false;
            foreach (PropertyInfo prop in cache.GetKeyProperties())
            {
                if (flag) builder.Append(" AND ");

                builder.Append("[");
                builder.Append(SqlFormatter.GetPropertyName(prop, "."));
                builder.Append("] = @");
                builder.Append(SqlFormatter.GetPropertyName(prop, ""));

                flag = true;
            }

            System.Data.SqlClient.SqlCommand command = connection.CreateCommand();
            command.CommandText = builder.ToString();
            command.ExecuteNonQuery();
            command.Dispose();
        }

        private string GetExistsProcedure(Type type)
        {
            StringBuilder builder = new StringBuilder();

            //Get the name for the type
            builder.Append(Common.GetTypeName(type, Prefix));
            builder.Append(".Exists");

            string procedure = builder.ToString();

            //Check to see if the procedure has been deployed
            if (_procedures.ContainsKey(procedure)) return procedure;

            System.Data.SqlClient.SqlConnection conn = GetConnectionInternal();

            try
            {
                if (!ProcedureExists(procedure, conn)) CreateExistsProcedure(conn, procedure, type);
            }
            finally
            {
                conn.Dispose();
            }

            _procedures.Add(procedure, true);

            return procedure;
        }

        private void CreateExistsProcedure(System.Data.SqlClient.SqlConnection connection, string name, Type type)
        {
            StringBuilder builder = new StringBuilder();

            builder.Append("CREATE PROCEDURE [");
            builder.Append(name);
            builder.Append("] ");
            builder.Append("\r\n");

            //Get the key cache
            ReflectionCacheItem cache = Component.Instance.ReflectionCache.Lookup(type);
            bool flag = false;

            foreach (PropertyInfo prop in cache.GetKeyProperties())
            {
                if (flag) builder.Append(",");

                builder.Append("@");
                builder.Append(SqlFormatter.GetPropertyName(prop, ""));
                builder.Append(" ");
                builder.Append(SqlCommon.SqlFromProperty(prop));

                flag = true;
            }

            builder.Append(" AS \r\nSELECT 1 FROM [");
            builder.Append(Common.GetTypeName(type, Prefix));
            builder.Append("] \r\nWHERE ");

            flag = false;
            foreach (PropertyInfo prop in cache.GetKeyProperties())
            {
                if (flag) builder.Append(" AND ");

                builder.Append("[");
                builder.Append(SqlFormatter.GetPropertyName(prop, "."));
                builder.Append("] = @");
                builder.Append(SqlFormatter.GetPropertyName(prop, ""));

                flag = true;
            }

            System.Data.SqlClient.SqlCommand command = connection.CreateCommand();
            command.CommandText = builder.ToString();
            command.ExecuteNonQuery();
            command.Dispose();
        }

        private string GetQueryProcedure(IQuery query)
        {
            StringBuilder builder = new StringBuilder();

            //Get the unqiue name and hashcode for the type
            builder.Append(Common.GetTypeName(query.ActivateType, Prefix));
            builder.Append(".Query");
            builder.Append(".");
            builder.Append(Common.GetQueryName(query));

            string procedure = builder.ToString();

            //Check to see if the procedure has been deployed
            if (_procedures.ContainsKey(procedure)) return procedure;

            System.Data.SqlClient.SqlConnection conn = GetConnectionInternal();

            try
            {
                if (!ProcedureExists(procedure, conn)) CreateQueryProcedure(conn, procedure, query);
            }
            finally
            {
                conn.Dispose();
            }

            _procedures.Add(procedure, true);

            return procedure;
        }

        private void CreateQueryProcedure(System.Data.SqlClient.SqlConnection connection, string name, IQuery query)
        {
            StringBuilder builder = new StringBuilder();

            //Get a list of aggregates form the entire query chain
            Columns aggregates = new Columns();
            GetQueryAggregates(query, aggregates);
            
            ReflectionCacheItem cache = Component.Instance.ReflectionCache.Lookup(query.ActivateType);
            PropertyInfo[] filter = null;

            //Check for a view, switch to persist type cache
            if (query.ActivateType != query.Type)
            {
                filter = cache.Properties;
                cache = Component.Instance.ReflectionCache.Lookup(query.Type);
            }

            //Create the from clause which will be used later by the query
            StringBuilder from = new StringBuilder();

            //Set abbreviations
            Labels labels = new Labels(query);
            QueryLabelHash queryLabel = new QueryLabelHash(query);
            TypeLabelHash typeLabel = new TypeLabelHash(query.Type, queryLabel);
            TypeLabelHash underlyingLabel = TypeLabelHash.GetBase(typeLabel);

            //Set up the from clause
            from.Append(" \r\nFROM [");
            from.Append(Common.GetTypeName(cache.UnderlyingType, Prefix));
            from.Append("] ");
            from.Append(labels[underlyingLabel]);

            //Join any base tables
            if (!cache.IsUnderlying) SelectBaseJoin(from, query.Type, cache, labels, typeLabel, JoinType.Inner);

            //Join for subqueries
            foreach (IQuery subquery in query.SubQueries)
            {
                SelectQueryJoin(from, query, cache, subquery, labels, queryLabel);
            }

            //Append any conditions to the where clause, including subquery conditions
            StringBuilder where = null;
            if (GetQueryConditions(query))
            {
                where = new StringBuilder();
                _flag = false;
                where.Append(" \r\nWHERE ");
                AppendQueryConditions(where, query, labels, queryLabel);
            }

            //Start the main procedure            
            builder.Append("CREATE PROCEDURE [");
            builder.Append(name);
            builder.Append("] \r\n");

            //Create a list to contain all the parameters for all queries
            List<string> parameters = new List<string>();

            _flag = false;
            AppendQueryParameterDefinitions(builder, query, parameters);

            if (parameters.Count > 0) builder.Append(" \r\n");
            builder.Append("AS");

            //Add select with distinct clause if required
            builder.Append(" \r\nSELECT ");

            //Append fields for all queries
            AppendQuerySelectProperties(builder, from, query, cache, labels, queryLabel, filter, aggregates);

            //Append from clause   
            builder.Append(from.ToString());

            //Append where clause
            if (where != null) builder.Append(where.ToString());

            //Append group by
            if (query.GroupBy != null && query.GroupBy.Count > 0)
            {
                _flag = false;
                builder.Append(" \r\nGROUP BY");
                AppendQueryAggregates(builder, query, labels, queryLabel);
            }

            //Append order by
            if (query.OrderBy != null && query.OrderBy.Count > 0)
            {
                _flag = false;
                builder.Append(" \r\nORDER BY");
                AppendQueryOrders(builder, query, labels, queryLabel);
            }

            System.Data.SqlClient.SqlCommand command = connection.CreateCommand();
            command.CommandText = builder.ToString();
            command.ExecuteNonQuery();
            command.Dispose();
        }

        private void AppendQuerySelectProperties(StringBuilder builder, StringBuilder from, IQuery query, ReflectionCacheItem cache, Labels labels, QueryLabelHash parent, PropertyInfo[] filter, IColumns aggregates)
        {
            if (query.Fill)
            {
                TypeLabelHash label = new TypeLabelHash(query.Type, parent);
                SelectPropertyFields(builder, query.Type, cache, labels, label, filter, aggregates);

                //Join any object properties
                if (cache.HasObjects || !cache.IsUnderlying) SelectObjectPropertyJoins(from, query.Type, cache, labels, label, filter);
            }

            //Process any sub queries
            if (query.SubQueries != null && query.SubQueries.Count > 0)
            {
                foreach (IQuery subquery in query.SubQueries)
                {
                    if (subquery.Fill) builder.Append(",\r\n");

                    ReflectionCacheItem subCache = Component.Instance.ReflectionCache.Lookup(subquery.Type);
                    QueryLabelHash subLabel = new QueryLabelHash(subquery, parent);
                    AppendQuerySelectProperties(builder, from, subquery, subCache, labels, subLabel, filter, aggregates);
                }
            }
        }

        private void AppendQueryParameterDefinitions(StringBuilder builder, IQuery query, List<string> parameters)
        {
            //Append the parameters for any conditions found for this query
            //Sub conditions will be handled by the procedure
            if (query.Where != null && query.Where.Count > 0) AppendQueryParameterDefinitionDetail(builder, query.Where, parameters);

            //Add any sub query conditions
            if (query.SubQueries != null && query.SubQueries.Count > 0)
            {
                foreach (IQuery subquery in query.SubQueries)
                {
                    AppendQueryParameterDefinitions(builder, subquery, parameters);
                }
            }
        }

        private void AppendQueryParameterDefinitionDetail(StringBuilder builder, IConditions conditions, List<string> parameters)
        {
            foreach (ICondition condition in conditions)
            {
                if (_flag) builder.Append(",");

                PropertyInfo prop = condition.PropertyInfo;

                builder.Append("@");
                builder.Append(SqlFormatter.AddUniqueParameter(SqlFormatter.GetPropertyName(prop, ""), parameters));
                builder.Append(" ");
                builder.Append(SqlCommon.SqlFromProperty(prop));

                _flag = true;
            }

            //Append sub conditions
            if (conditions.SubConditions != null) AppendQueryParameterDefinitionDetail(builder, conditions.SubConditions, parameters);
        }

        private void SelectQueryJoin(StringBuilder builder, IQuery query, ReflectionCacheItem cache, IQuery subquery, Labels labels, QueryLabelHash parent)
        {
            //Work out the labels
            TypeLabelHash label = new TypeLabelHash(query.Type, parent);
            QueryLabelHash child = new QueryLabelHash(subquery, parent);
            TypeLabelHash subLabel = new TypeLabelHash(subquery.Type, child);

            builder.Append(" \r\n");
            builder.Append(SqlCommon.SqlFromJoin(query.JoinType));
            builder.Append("JOIN [");
            builder.Append(Common.GetTypeName(subquery.Type, Prefix));
            builder.Append("] ");
            builder.Append(labels[subLabel]);
            builder.Append(" ON ");

            ReflectionCacheItem subcache = Component.Instance.ReflectionCache.Lookup(subquery.Type);

            int i = 0;

            foreach (IColumn field in subquery.JoinFrom)
            {
                if (i > 0) builder.Append(" AND ");

                builder.Append(labels[label]);
                builder.Append(".[");
                builder.Append(SqlFormatter.GetPropertyName(field.PropertyInfo, "."));
                builder.Append("] = ");
                builder.Append(labels[subLabel]);
                builder.Append(".[");
                builder.Append(SqlFormatter.GetPropertyName(subquery.JoinTo[i].PropertyInfo, "."));
                builder.Append("]");

                i++;
            }

            //Join any sub query base tables
            if (!subcache.IsUnderlying) SelectBaseJoin(builder, subquery.Type, subcache, labels, subLabel, JoinType.Inner);
            
            //Join sub query to any sub queries
            if (query.SubQueries != null && query.SubQueries.Count > 0)
            {
                foreach (IQuery subsubquery in subquery.SubQueries)
                {
                    SelectQueryJoin(builder, subquery, subcache, subsubquery, labels, child);
                }
            }
        }

        private void AppendQueryConditions(StringBuilder builder, IQuery query, Labels labels, QueryLabelHash parent)
        {
            //Add the terms for conditions including sub conditions
            if (query.Where != null && query.Where.Count > 0) AppendQueryConditionsDetail(builder, query, query.Where, labels, parent); 

            //Add sub query conditions
            if (query.SubQueries != null && query.SubQueries.Count > 0)
            {
                foreach (IQuery subquery in query.SubQueries)
                {
                    QueryLabelHash child = new QueryLabelHash(subquery, parent);
                    AppendQueryConditions(builder, subquery, labels, child);
                }
            }
        }

        private void AppendQueryConditionsDetail(StringBuilder builder, IQuery query, IConditions conditions, Labels labels, QueryLabelHash parent)
        {
            foreach (ICondition condition in conditions)
            {
                //Append operand
                if (_flag)
                {
                    if (conditions.Operator == Operator.And)
                    {
                        builder.Append(" AND ");
                    }
                    else
                    {
                        builder.Append(" OR ");
                    }
                }

                //Append abbreviation
                TypeLabelHash label = GetPropertyLabel(query, parent, condition.PropertyInfo);

                //Lookup labels
                if (label == null || !labels.ContainsKey(label)) throw new PersistenceException("Condition contains Property " + condition.PropertyInfo.ToString() + " which is not found in the query.");
                builder.Append(labels[label]);
                builder.Append(".[");

                //Append field name
                builder.Append(SqlFormatter.GetPropertyName(condition.PropertyInfo, "."));
                builder.Append("]");

                //Append comparison
                if (condition.Comparison == Comparison.Equals)
                {
                    builder.Append(" = ");
                }
                else if (condition.Comparison == Comparison.GreaterThan)
                {
                    builder.Append(" > ");
                }
                else if (condition.Comparison == Comparison.GreaterThanEquals)
                {
                    builder.Append(" >= ");
                }
                else if (condition.Comparison == Comparison.LessThan)
                {
                    builder.Append(" < ");
                }
                else if (condition.Comparison == Comparison.LessThanEquals)
                {
                    builder.Append(" <= ");
                }
                else if (condition.Comparison == Comparison.NotEquals)
                {
                    builder.Append(" <> ");
                }
                else if (condition.Comparison == Comparison.StartsWith || condition.Comparison == Comparison.Contains)
                {
                    builder.Append(" LIKE ");
                }

                //Add parameter
                builder.Append("@");
                builder.Append(SqlFormatter.GetPropertyName(condition.PropertyInfo, ""));

                _flag = true;
            }

            if (conditions.SubConditions != null)
            {
                //Append operand
                if (_flag)
                {
                    if (conditions.Operator == Operator.And)
                    {
                        builder.Append(" AND ");
                    }
                    else
                    {
                        builder.Append(" OR ");
                    }
                }

                builder.Append("(");
                AppendQueryConditionsDetail(builder, query, conditions.SubConditions, labels, parent);
                builder.Append(")");

                _flag = true;
            }
        }
       
        //Determines if a query has nested conditions
        private bool GetQueryConditions(IQuery query)
        {
            if (query.Where != null && query.Where.Count > 0) return true;
            if (query.SubQueries != null && query.SubQueries.Count > 0)
            {
                foreach (IQuery subquery in query.SubQueries)
                {
                    if (GetQueryConditions(subquery)) return true;
                }
            }
            return false;
        }

        private void AppendQueryOrders(StringBuilder builder, IQuery query, Labels labels, QueryLabelHash parent)
        {
            foreach (IColumn field in query.OrderBy)
            {
                ISort sort = null;

                if (field is ISort) sort = field as ISort;

                if (_flag) builder.Append(", ");

                TypeLabelHash label = GetPropertyLabel(query, parent, field.PropertyInfo);

                //Append abbreviation
                if (!labels.ContainsKey(label)) throw new PersistenceException("Sort field contains Type " + sort.PropertyInfo.ToString() + " which is not found in the query.");
                builder.Append(labels[label]);
                builder.Append(".[");

                //Append field name
                builder.Append(SqlFormatter.GetPropertyName(field.PropertyInfo, "."));
                builder.Append("]");

                if (sort != null && sort.Direction == SortDirection.Descending) builder.Append(" DESC");

                _flag = true;
            }
        }

        private void AppendQueryAggregates(StringBuilder builder, IQuery query, Labels labels, QueryLabelHash parent)
        {
            foreach (IColumn field in query.GroupBy)
            {
                IAggregate aggregate = null;
                if (field is IAggregate) aggregate = field as IAggregate;

                if (aggregate == null || aggregate.AggregateType == AggregateType.Unique)
                {
                    if (_flag) builder.Append(", ");

                    TypeLabelHash label = GetPropertyLabel(query, parent, aggregate.PropertyInfo);

                    //Append abbreviation
                    if (!labels.ContainsKey(label)) throw new PersistenceException("Sort contains Type " + aggregate.PropertyInfo.ToString() + " which is not found in the query.");
                    builder.Append(labels[label]);
                    builder.Append(".[");

                    //Append field name
                    builder.Append(SqlFormatter.GetPropertyName(aggregate.PropertyInfo, "."));
                    builder.Append("]");

                    _flag = true;
                }
            }
        }

        //Writes out parameters from base class back up inheritance chain
        private void GetUpdateProcedureParameters(StringBuilder builder, Type type, ReflectionCacheItem cache, bool concurrency)
        {
            bool flag = false;

            foreach (PropertyInfo prop in cache.GetKeyProperties())
            {
                if (flag) builder.Append(",");

                //Get the type of the key
                builder.Append("@");
                builder.Append(SqlFormatter.GetPropertyName(prop, ""));
                builder.Append(" ");
                builder.Append(SqlCommon.SqlFromProperty(prop));

                flag = true;
            }

            foreach (PropertyInfo prop in cache.Properties)
            {
                if (flag) builder.Append(",");

                //Add parameter for prop
                builder.Append("@");
                builder.Append(SqlFormatter.GetPropertyName(prop, ""));
                builder.Append(" ");
                builder.Append(SqlCommon.SqlFromProperty(prop));

                //Add concurrency parameter
                if (concurrency && Deployment.GetTypeConcurrency(prop.PropertyType))
                {
                    builder.Append(",@");
                    builder.Append(SqlFormatter.GetPropertyName(prop, ""));
                    builder.Append("Old ");
                    builder.Append(SqlCommon.SqlFromProperty(prop));
                }
            }
        }

        private string GetAggregate(PropertyInfo prop, IColumns aggregates)
        {
            if (aggregates == null || aggregates.Count == 0) return null;
            foreach (IColumn field in aggregates)
            {
                if (field is IAggregate)
                {
                    IAggregate aggregate = field as IAggregate;

                    if (aggregate.AggregateType != AggregateType.Unique && aggregate.PropertyInfo == prop)
                    {
                        if (aggregate.AggregateType == AggregateType.Avg) return "AVG";
                        if (aggregate.AggregateType == AggregateType.Max) return "MAX";
                        if (aggregate.AggregateType == AggregateType.Min) return "MIN";
                        if (aggregate.AggregateType == AggregateType.Sum) return "SUM";
                        if (aggregate.AggregateType == AggregateType.Count) return "COUNT";
                    }
                }
            }
            return null;
        }

        private void GetQueryAggregates(IQuery query, IColumns aggregates)
        {
            if (query.GroupBy != null && query.GroupBy.Count > 0)
            {
                foreach (IColumn aggregate in query.GroupBy)
                {
                    aggregates.Add(aggregate);
                }
            }

            //Loop through and add any child aggregates
            if (query.SubQueries != null && query.SubQueries.Count > 0)
            {
                foreach (IQuery subquery in query.SubQueries)
                {
                    GetQueryAggregates(subquery, aggregates);
                }
            }
        }

        private TypeLabelHash GetPropertyLabel(IQuery query, QueryLabelHash parent, PropertyInfo prop)
        {
            //If property is from a view then convert to original prop
            if (typeof(IView).IsAssignableFrom(prop.DeclaringType))
            {
                ReflectionCacheItem viewCache = Component.Instance.ReflectionCache.Lookup(prop.DeclaringType);
                prop = viewCache.GetOriginalProperty(prop);
            }

            Type type = query.Type;

            //Start with the query type and loop until we get the property type
            TypeLabelHash label = new TypeLabelHash(type, parent);

            if (prop.DeclaringType == type) return label;

            return GetPropertyLabelInherited(type, label, prop);
        }

        private TypeLabelHash GetPropertyLabelInherited(Type type, LabelHash parent, PropertyInfo prop)
        {
            ReflectionCacheItem cache = Component.Instance.ReflectionCache.Lookup(type);

            if (cache.IsUnderlying) throw new PersistenceException("Property " + prop.ToString() + " was not found in the query.");

            TypeLabelHash label = new TypeLabelHash(cache.BaseType, parent);

            if (prop.DeclaringType == cache.BaseType) return label;

            return GetPropertyLabelInherited(cache.BaseType, label, prop);
        }

        #endregion
    }
}
